﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Ebiz.libs;
using System.Collections.Specialized;
using System.Web.Services;
using System.Web.Script.Services;
using System.Net.Mail;
using System.Net;

namespace Ebiz
{
    public partial class BulkView : System.Web.UI.Page
    {
        public enum DistributorField{
            CODE, NAME
        }
        public enum EmailEngine{
            CDOSYS, NETMAIL
        }
        public enum EmailMode{
            REQUEST, APPROVED, RETURNED, REJECTED
        }
        public enum CLRole { 
            REQUESTER, APPROVAL
        }
        public string clStatus = "";
        public string strDocList = "";
        public string strAppList = "";
        private string SendCode = "";
        public string btnAppv = "";
        string _CLNum = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request.QueryString["cl"] != null &&
                !(string.IsNullOrEmpty(Request.QueryString["cl"]))){

                SendCode = Request.QueryString["cl"];

                string DecryptQry = CryptLib.Base64(Request.QueryString["cl"], CryptMode.DECRYPT);
                NameValueCollection ResX = HttpUtility.ParseQueryString(DecryptQry);
                int ResXLen = ResX.Count;
                if (ResXLen >= 2){
                    string DecryptCLNum = ResX[0].ToString();
                    string DecryptToken = ResX[1].ToString();
                    _CLNum = DecryptCLNum;
                    /*Response.Write(DecryptQry + "<br />");
                    Response.Write(Func.GetUserInfo(Func.ShowInfo._UserID));
                    Response.End();*/

                    if (string.IsNullOrEmpty(DecryptCLNum) || string.IsNullOrEmpty(DecryptToken)){
                        Session["ErrCode"] = "ES-404";
                        Response.Redirect("ErrorPage.aspx", true);
                    }else{
                        if (CheckRequest(DecryptCLNum, DecryptToken) > 0){
                            string CLNumber = DecryptCLNum;

                            ShowButton(CLNumber);
                            hdViewer.Value = Func.GetUserInfo(Func.ShowInfo._UserID);

                            txtCLNumber.Text = CLNumber;
                            string DistributorCode = GetDistributorInfo(CLNumber, DistributorField.CODE);
                            string DistributorName = GetDistributorInfo(CLNumber, DistributorField.NAME);
                            
                            hidDistributor.Value = DistributorCode;
                            txtDistributor.Text = DistributorName;

                            strAppList = ApprovalList(CLNumber);
                            strDocList = DocumentList(CLNumber);
                            
                        }else{
                            Session["ErrCode"] = "ES-404";
                            Response.Redirect("ErrorPage.aspx", true);
                        }
                    }
                }else{
                    Session["ErrCode"] = "ES-404";
                    Response.Redirect("ErrorPage.aspx", true);
                }
            }else{
                Session["ErrCode"] = "ES-404";
                Response.Redirect("ErrorPage.aspx", true);
            }
        }

        private void ShowButton(string CLNum) {

            string myID = Func.GetUserInfo(Func.ShowInfo._UserID);
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    string qry = "SELECT CLNum, ApprovalCurrent, CreatedBy, StatusCode " + 
                        "FROM " + DBName + ".dbo.tbl_Request WHERE " + 
                        "(CLNum = @CLNum)";
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            string AppPerson = rdr.GetValue(1).ToString();
                            string Requester = rdr.GetValue(2).ToString();
                            string StatusCode = rdr.GetValue(3).ToString();

                            switch (StatusCode) { 
                                case "210":
                                    clStatus = "ON PROGRESS";
                                    if (AppPerson == myID) {
                                        btnAppv = "<input type='button' name='btnSetApv' id='btnApprove'" +
                                            "value='SET APPROVAL' />";
                                    }
                                    break;
                                case "213":
                                    clStatus = "RETURN FOR INFORMATION";
                                    if (Requester == myID) {
                                        btnEdit.Visible = true;
                                    }
                                    break;
                                case "211":
                                    clStatus = "APPROVED";
                                    if (Requester == myID) {
                                        btnDownload.Visible = true;
                                    }
                                    break;
                                case "214":
                                    clStatus = "REJECTED";
                                    break;
                            }
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        private static int CheckRequest(string CLNumber, string SSID){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string dbName = DBUtilities.GetDBName("devCon");
            using (cn){
                string qry = "SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_Request WHERE " +
                    "(CLNum = @CLNum) AND (Token = @Token) AND (StatusCode NOT IN('200','201','203','204'))";
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    cmd.Parameters.AddWithValue("@Token", SSID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = Convert.ToInt32(rdr.GetValue(0));
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }
        
        private static string GetDistributorInfo(string CLNumber, DistributorField distributorField) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_Request.CLNum, tbl_Request.DistributorCode, " + 
                "mstDistributor.DistributorName " +
                "FROM " + dbName + ".dbo.tbl_Request " +
                "INNER JOIN " + dbName + ".dbo.mstDistributor " + 
                "ON tbl_Request.DistributorCode = mstDistributor.DistributorCode " +
                "WHERE (CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            string strCode = rdr.GetValue(1).ToString();
                            string strName = rdr.GetValue(2).ToString();
                            switch (distributorField){
                                case DistributorField.CODE:
                                    result = strCode;
                                    break;
                                case DistributorField.NAME:
                                    result = strName;
                                    break;
                            }
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetCLStatus(string StatusCode, string UserID){
            string result = "";

            Dictionary<string, string> tmpResult = Func.GetStatusInfo(StatusCode);
            switch (tmpResult["CODE"]){
                case "100":
                case "101":
                case "110":
                case "111":
                    result = "On Progress";
                    break;
                case "210":
                    string ApvName = GetFullName(UserID);
                    result = tmpResult["DESCRIPTION"] + " by " + ApvName;
                    break;
                default:
                    result = tmpResult["DESCRIPTION"];
                    break;
            }
            return result;
        }

        private static string GetFullName(string UserID){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT FullName FROM " + dbName + ".dbo.UserAccounts WHERE " +
                "(UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string DocumentList(string CLNumber){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT FileLocation, FileName FROM " + dbName + ".dbo.tbl_RequestFiles WHERE " +
                "(CLNum = @CLNum) AND (StatusCode = '101')";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        result = "<ul id=\"olDoc\">\n";
                        while (rdr.Read()){
                            string FolderLoc = rdr.GetValue(0).ToString();
                            string[] FileName = rdr.GetValue(1).ToString().Split('\\');
                            string FileLink = FolderLoc + FileName[FileName.Count()-1];
                            string Target = "target=\"_blank\"";
                            string ALink = "<a href=\"" + FileLink + "\" " + Target + ">";
                            ALink += FileName[FileName.Count() - 1] + "</a>";

                            string tmpResult = "<li>" + ALink + "</li>\n";

                            result += tmpResult;
                        }
                        result += "</ul>\n";
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string ApprovalList(string CLNumber){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT CLNum, CurrentStep, " + dbName + ".dbo.UserAccounts.FullName, Notes, " +
                dbName + ".dbo.tbl_RequestApproval.StatusCode " +
                "FROM " + dbName + ".dbo.tbl_RequestApproval INNER JOIN " + dbName +
                ".dbo.UserAccounts ON " + dbName + ".dbo.UserAccounts.UserName = " + 
                "tbl_RequestApproval.CurrentStep WHERE " +
                "(CLNum = @CLNum)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        result = "<ol id=\"olApv\">\n";
                        while (rdr.Read()){
                            string ApvCode = rdr.GetValue(1).ToString();
                            string ApvName = rdr.GetValue(2).ToString();
                            string _Notes = rdr.GetValue(3).ToString();
                            string _Status = rdr.GetValue(4).ToString();
                            string Notes = (string.IsNullOrEmpty(_Notes)) ? "" : "<span class=\"spnNote\" title=\"View Notes\">View Notes</span>";
                            string StatusCode = "";
                            switch (_Status){
                                case "210":
                                    StatusCode = "<span class=\"spnStatus\">Status: Waiting for Approval</span>";
                                    break;
                                case "211":
                                    StatusCode = "<span class=\"spnStatus\">Status: Approved</span>";
                                    break;
                                case "213":
                                    StatusCode = "<span class=\"spnStatus\">Status: Returned for Information</span>";
                                    break;
                                case "214":
                                    StatusCode = "<span class=\"spnStatus\">Status: Rejected</span>";
                                    break;
                            }
                            string tmpResult = "<li><input type=\"hidden\" value=\"" + ApvCode + "\" />" +
                                ApvName + StatusCode + Notes + "</li>\n";

                            result += tmpResult;
                        }
                        result += "</ol>\n";
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> ViewNotes(string IDReq, string IDApv){
            List<object> result = new List<object>();
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT CLNum, CurrentStep, Notes, " + dbName + ".dbo.UserAccounts.FullName " +
                "FROM " + dbName + ".dbo.tbl_RequestApproval INNER JOIN " + dbName +
                ".dbo.UserAccounts ON CurrentStep = " + dbName + ".dbo.UserAccounts.UserName WHERE " +
                "(CLNum = @CLNum) AND (CurrentStep = @CurrentStep)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", IDReq);
                    cmd.Parameters.AddWithValue("@CurrentStep", IDApv);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            string Notes = rdr.GetValue(2).ToString();
                            string ApvName = rdr.GetValue(3).ToString();
                            result.Add(1);
                            result.Add(ApvName);
                            result.Add(Notes);
                        }else{
                            result.Add(0);
                            result.Add("No data found!");
                            result.Add("");
                        }
                    }else{
                        result.Add(0);
                        result.Add("No data found!");
                        result.Add("");
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                    result.Add(0);
                    result.Add(errMsg);
                    result.Add("");
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetEmailAddress(string UserID){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT Email FROM " + dbName + ".dbo.UserAccounts WHERE " +
                "(UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static Dictionary<string, object> GetEmailConfig(){
            Dictionary<string, object> result = new Dictionary<string, object>();

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT ConfigName, ConfigValue, Description FROM " + dbName + 
                ".dbo.mstConfig WHERE (ConfigGroup = 'Email')";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string CFGName = rdr.GetValue(0).ToString();
                            string CFGValue = rdr.GetValue(1).ToString();
                            string CFGDesc = rdr.GetValue(2).ToString();

                            result.Add(CFGName, CFGValue);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach (KeyValuePair<string, object> kvp in Params){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> SetApproval(string IDCL, string ApvStatus, 
            string ApvNotes){

            int Status = 0;
            string Response = "";

            Dictionary<string, object> result = new Dictionary<string, object>();
            string apvPerson = Func.GetUserInfo(Func.ShowInfo._UserID);
            Dictionary<string, object> dctDataCL = CLData(IDCL);
            
            int Step = (int)dctDataCL["Step"];
            int Next = (int)dctDataCL["Next"];
            int Max = (int)dctDataCL["Max"];
            string NextApvID = "0";

            Dictionary<string, object> dctSendMail = new Dictionary<string, object>();
            Dictionary<string, string> dctEmail = new Dictionary<string, string>();
            if ((string)dctDataCL["ApvID"] == apvPerson) {
                int MailSent = 0;
                dctEmail.Add("CLNumber", IDCL);
                dctEmail.Add("ReqName", dctDataCL["ReqName"].ToString());
                dctEmail.Add("Token", dctDataCL["Token"].ToString());

                string ApvCode = "";
                bool UpdateMasterStat = true;
                switch (ApvStatus) { 
                    case "1":
                        ApvCode = "211";
                        // Approved
                        if (Step == Max){ // IF Current Step = Max Step
                            //ApvCode = "211";

                            // Send Email to Requester Only
                            dctEmail.Add("MailTo", dctDataCL["ReqEmail"].ToString());
                            dctEmail.Add("MailCC", "");
                            dctEmail.Add("ApvName", "");
                            dctSendMail = SendEmail(dctEmail, CLSMail.MessageMode.APPROVED);

                        }else if (Step < Max){ // IF Next Step < Max Step
                            //ApvCode = "210";
                            UpdateMasterStat = false;
                            NextApvID = GetNextApvID(IDCL, Next, dctDataCL["NextApvID"].ToString());
                            
                            // Send Email to Requester and Next Approval Person
                            dctEmail.Add("MailTo", dctDataCL["NextApvEmail"].ToString());
                            dctEmail.Add("MailCC", dctDataCL["ReqEmail"].ToString());
                            dctEmail.Add("ApvName", dctDataCL["NextApvName"].ToString());
                            dctSendMail = SendEmail(dctEmail, CLSMail.MessageMode.REQUEST);
                        }
                        
                        break;
                    case "2":
                        // Return for Information
                        ApvCode = "213";
                        
                        // Send Email to Requester Only
                        dctEmail.Add("MailTo", dctDataCL["ReqEmail"].ToString());
                        dctEmail.Add("MailCC", "");
                        dctEmail.Add("ApvName", "");
                        dctSendMail = SendEmail(dctEmail, CLSMail.MessageMode.RETURNED);
                        break;
                    case "3":
                        // Reject
                        ApvCode = "214";
                        
                        // Send Email to Requester Only
                        dctEmail.Add("MailTo", dctDataCL["ReqEmail"].ToString());
                        dctEmail.Add("MailCC", "");
                        dctEmail.Add("ApvName", "");
                        dctSendMail = SendEmail(dctEmail, CLSMail.MessageMode.REJECTED);
                        break;
                }
                
                // If Sending Email Success ------------------
                // Update tbl_RequestApproval > Set EmailSent = 1 and Update EmailDate
                MailSent = (int)dctSendMail["Status"];
                if (MailSent == 1) {
                    // Update tbl_Request. Change StatusCode if UpdateMasterStat = true
                    string DBName = DBUtilities.GetDBName("devCon");
                    string EditStatusCode = (UpdateMasterStat == true) ? ", StatusCode = '" + ApvCode + "'" : "";
                    string qryMaster = "UPDATE " + DBName + ".dbo.tbl_Request " +
                        "SET ApprovalStep = @Next, ApprovalCurrent = @ApprovalCurrent, " +
                        "ApprovalNext = @ApprovalNext" + EditStatusCode + " WHERE " +
                        "(CLNum = @CLNum)";
                    Dictionary<string, object> dctMaster = new Dictionary<string, object>();
                    dctMaster.Add("Next", Next);
                    dctMaster.Add("ApprovalCurrent", dctDataCL["NextApvID"].ToString());
                    dctMaster.Add("ApprovalNext", NextApvID);
                    dctMaster.Add("CLNum", IDCL);

                    if (ExecQuery(qryMaster, dctMaster) > 0){
                        // Update tbl_RequestApproval
                        string qrySub = "UPDATE " + DBName + ".dbo.tbl_RequestApproval " +
                            "SET Notes = @Notes, StatusCode = @StatusCode " +
                            "WHERE (CLNum = @CLNum) AND (StepSeq = @StepSeq) " +
                            "AND (CurrentStep = @CurrentStep)";
                        Dictionary<string, object> dctSub = new Dictionary<string, object>();
                        dctSub.Add("CLNum", IDCL);
                        dctSub.Add("StepSeq", Step);
                        dctSub.Add("CurrentStep", dctDataCL["ApvID"].ToString());
                        dctSub.Add("Notes", ApvNotes);
                        dctSub.Add("StatusCode", ApvCode);
                        if (ExecQuery(qrySub, dctSub) > 0){// If success, throw success message 
                            Status = 1;
                            Response = "Update Data Success!";
                        }else{
                            // Throw Error Message
                            Response = "Update Data Failed!";
                        }
                    }else{
                        // Throw Error Message
                        Response = "Update Data Failed!";
                    }
                }else{
                    Response = dctSendMail["Message"].ToString();
                }
            }else{
                // Hack Possibility
                Response = "Internal System Error!";
            }
            
            result.Add("Status", Status);
            result.Add("Response", Response);

            return result;
        }

        private static Dictionary<string, object> SendEmail(Dictionary<string,string> EmailParams,
            CLSMail.MessageMode SendMode){

            Dictionary<string, object> result = new Dictionary<string, object>();
            string EmailAddress = EmailParams["MailTo"];
            string EmailSubject = "BizCase request with CL Number " + EmailParams["CLNumber"];
            if (!(string.IsNullOrEmpty(EmailAddress))){
                Dictionary<string, object> dctConfig = GetEmailConfig();

                MailMessage Mailer = new MailMessage();
                Mailer.Subject = EmailSubject;
                Mailer.From = new MailAddress(dctConfig["FromEmail"].ToString(), dctConfig["FromName"].ToString());
                Mailer.Priority = (MailPriority)Convert.ToInt32(dctConfig["Priority"]);
                Mailer.IsBodyHtml = (bool)Convert.ToBoolean(dctConfig["IsBodyHTML"]);
                
                Mailer.To.Add(EmailAddress);
                if (EmailParams.ContainsKey("MailCC")) {
                    if(EmailParams["MailCC"] != string.Empty){
                        Mailer.CC.Add(EmailParams["MailCC"]);
                    }
                }
                
                Dictionary<string, string> dctEmail = new Dictionary<string, string>();
                dctEmail["Subject"] = EmailSubject;
                dctEmail["Requester"] = EmailParams["ReqName"];
                dctEmail["AppvPerson"] = EmailParams["ApvName"];
                dctEmail["AppvPersonExt"] = "";
                dctEmail["CLNum"] = EmailParams["CLNumber"];
                dctEmail["Link"] = generatePageLink(EmailParams["CLNumber"], EmailParams["Token"]);
                Mailer.Body = CLSMail.GenerateMessage(dctEmail, SendMode);

                SmtpClient _SMTP = new SmtpClient();
                _SMTP.DeliveryMethod = (SmtpDeliveryMethod)Convert.ToInt32(dctConfig["DeliveryMethod"]);
                _SMTP.UseDefaultCredentials = (bool)Convert.ToBoolean(dctConfig["DefaultCredentials"]);
                if (!(dctConfig["UserName"] == null && dctConfig["Password"] == null)){
                    _SMTP.Credentials = new NetworkCredential(dctConfig["UserName"].ToString(), dctConfig["Password"].ToString());
                }
                _SMTP.Host = (string)dctConfig["Host"].ToString();
                _SMTP.Port = (int)Convert.ToInt32(dctConfig["Port"]);
                _SMTP.EnableSsl = (bool)Convert.ToBoolean(dctConfig["EnableSSL"]);
                _SMTP.Timeout = (int)Convert.ToInt32(dctConfig["Timeout"]);

                try{
                    _SMTP.Send(Mailer);
                    result["Status"] = 1;
                    result["Message"] = "Email Sent!";
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                    result["Status"] = 0;
                    result["Message"] = ErrMsg;
                }
            }else{
                result["Status"] = 0;
                result["Message"] = "Email address is empty!";
            }
            return result;
        }

        private static string generatePageLink(string CLNum, string SSID){
            string result = "";

            Dictionary<string, object> dctSYS = Func.GetSysConfig();
            string SYSPort = "/";
            string SYSUrl = dctSYS["ServerAddress"].ToString() + SYSPort;
            string PageQry = "id=" + CLNum + "&atdn=" + SSID;

            result = SYSUrl + "Default.aspx?cl=" + CryptLib.Base64(PageQry, CryptMode.ENCRYPT);

            return result;
        }

        private static string GetNextApvID(string CLNumber, int StepSeq, string CurrentStep) {
            string result = "";
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();
                    string qry = "SELECT NextStep FROM " + DBName + ".dbo.tbl_RequestApproval " +
                        "WHERE (CLNum = @CLNum) AND (StepSeq = @StepSeq) " +
                        "AND (CurrentStep = @CurrentStep)";
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("CLNum", CLNumber);
                    cmd.Parameters.AddWithValue("StepSeq", StepSeq);
                    cmd.Parameters.AddWithValue("CurrentStep", CurrentStep);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch(Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if(cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static Dictionary<string, object> CLData(string CLNumber) {
            Dictionary<string, object> result = new Dictionary<string, object>();
            string DBName = DBUtilities.GetDBName("devCon");
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();
                    string qry = "SELECT tbl_Request.ApprovalStep, tbl_Request.ApprovalMax, " +
                    "tbl_Request.CreatedBy, tbl4.FullName, tbl4.Email, " +
                    "tbl_Request.ApprovalCurrent, tbl2.FullName, tbl2.Email, " +
                    "tbl_Request.ApprovalNext, tbl3.FullName, tbl3.Email, " +
                    "tbl_Request.StatusCode, tbl_Request.Token " +
                    "FROM " + DBName + ".dbo.tbl_Request " +
                    "LEFT OUTER JOIN " + DBName + ".dbo.UserAccounts AS tbl2 " +
                    "ON tbl2.UserName = tbl_Request.ApprovalCurrent " +
                    "LEFT OUTER JOIN " + DBName + ".dbo.UserAccounts AS tbl3 " +
                    "ON tbl3.UserName = tbl_Request.ApprovalNext " +
                    "LEFT OUTER JOIN " + DBName + ".dbo.UserAccounts AS tbl4 " +
                    "ON tbl4.UserName = tbl_Request.CreatedBy " +
                    "WHERE (tbl_Request.CLNum = @CLNum) AND (tbl_Request.StatusCode = '210')";
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            int Step = (int)rdr.GetValue(0);
                            int Max = (int)rdr.GetValue(1);
                            int Next = (Step < Max) ? (Step + 1) : Max;
                            string RequesterID = rdr.GetValue(2).ToString();
                            string RequesterFName = rdr.GetValue(3).ToString();
                            string RequesterEmail = rdr.GetValue(4).ToString();
                            string ApvID = rdr.GetValue(5).ToString();
                            string ApvFName = rdr.GetValue(6).ToString();
                            string ApvEmail = rdr.GetValue(7).ToString();
                            string NextApvID = rdr.GetValue(8).ToString();
                            string NextApvFName = rdr.GetValue(9).ToString();
                            string NextApvEmail = rdr.GetValue(10).ToString();
                            string StatusCode = rdr.GetValue(11).ToString();
                            string Token = rdr.GetValue(12).ToString();

                            result.Add("Step", Step);
                            result.Add("Next", Next);
                            result.Add("Max", Max);
                            result.Add("ReqID", RequesterID);
                            result.Add("ReqName", RequesterFName);
                            result.Add("ReqEmail", RequesterEmail);
                            result.Add("ApvID", ApvID);
                            result.Add("ApvName", ApvFName);
                            result.Add("ApvEmail", ApvEmail);
                            result.Add("NextApvID", NextApvID);
                            result.Add("NextApvName", NextApvFName);
                            result.Add("NextApvEmail", NextApvEmail);
                            result.Add("StatusCode", StatusCode);
                            result.Add("Token", Token);
                        }
                    }

                }catch(Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            #region depreciated line
            /*
            string qryx = "SELECT tbl_Request.ApprovalStep, tbl_Request.ApprovalMax, " +
                "tbl_Request.CreatedBy, tbl4.FullName, tbl4.Email, " +
                "tbl_Request.ApprovalCurrent, tbl2.FullName, tbl2.Email, " +
                "tbl_Request.ApprovalNext, tbl3.FullName, tbl3.Email, " +
                "tbl_Request.StatusCode, tbl_Request.Token " +
                "FROM " + DBName + ".dbo.tbl_Request " +
                "LEFT OUTER JOIN " + DBName + ".dbo.UserAccounts AS tbl2 " +
                "ON tbl2.UserName = tbl_Request.ApprovalCurrent " +
                "LEFT OUTER JOIN " + DBName + ".dbo.UserAccounts AS tbl3 " +
                "ON tbl3.UserName = tbl_Request.ApprovalNext " +
                "LEFT OUTER JOIN " + DBName + ".dbo.UserAccounts AS tbl4 " +
                "ON tbl4.UserName = tbl_Request.CreatedBy " +
                "WHERE (tbl_Request.CLNum = '" + CLNumber + "') AND (tbl_Request.StatusCode = '210')";
            result.Add("Status", 0);
            result.Add("Message", qryx);
            */
            #endregion
            return result;
        }

        protected void btnBack_Click(object sender, EventArgs e)
        {
            Response.Redirect("Home.aspx", true);
        }

        protected void btnEdit_Click(object sender, EventArgs e)
        {
            string pTarget = "EditBulkUpload.aspx?cl=" + _CLNum;
            Response.Redirect(pTarget, true);
        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string pTarget = "DownloadBulk.aspx?dlcl=" + _CLNum;
            Response.Redirect(pTarget, true);
        }
    }
}
